Data Transformation

Part 2

Table of Contents

• Add new variables with mutate()
• Grouped summaries with summarize()
• Group by with group_by()

Add new variables with mutate()

Besides selecting sets of existing columns, it’s often useful to add new columns that are functions of existing columns. That’s the job of mutate().

mutate() always adds new columns at the end of your dataset, but doesn’t change the original dataframe. If you want to keep the outcome of mutate(), or any of the other functions we discussed in the previous notebook, assign the output to a new or existing object.

# Starting with a smaller number of columns
flights_sml <- select(flights, 
                      year:day, 
                      ends_with("delay"), 
                      distance, 
                      air_time
                     )

flights_sml

mutate(flights_sml,
       gain = dep_delay - arr_delay,
       speed = distance / air_time * 60
      )

Alternatively we could’ve used our piping skills:

flights %>%
  select(year:day, 
         ends_with("delay"), 
         distance, 
         air_time
        ) %>%
  mutate(gain = dep_delay - arr_delay,
         speed = distance / air_time * 60)

Note that you can refer to columns that you’ve just created:

mutate(flights_sml,
       gain = dep_delay - arr_delay,
       hours = air_time / 60,
       gain_per_hour = gain / hours
      )

---

Grouped summaries with summarize()

The last key verb is summarize() (or summarise()). It collapses a data frame to a single row:

# Average delay
summarize(flights, delay = mean(dep_delay, na.rm = TRUE))

What happens if we don’t specify na.rm = TRUE?

group_by()

summarize() is not terribly useful unless we pair it with group_by(). This changes the unit of analysis from the complete dataset to individual groups. Then, when you use the dplyr verbs on a grouped data frame they’ll automatically be applied “by group”. For example, if we applied exactly the same code to a data frame grouped by date, we get the average delay per date:

# Average delay for each day
flights %>%
  group_by(year, month, day) %>%
  summarize(delay = mean(dep_delay, na.rm = TRUE))

`summarise()` regrouping output by 'year', 'month' (override with `.groups` argument)

Examples

How does the average distance to destination impact the arrival delay on average?

delay <- flights %>% 
  group_by(dest) %>%  # group by destination
  summarize(count = n(),  # counting the number of flights (per destination)
            Distance = mean(distance, na.rm = TRUE),  # average distance (per destination)
            Delay = mean(arr_delay, na.rm = TRUE)  # average delay (per destination)
           ) %>%
  filter(count > 20, dest != "HNL")  # keeping destinations with more than 20 flights that are not "HNL"

`summarise()` ungrouping output (override with `.groups` argument)

delay

p <- ggplot(data = delay, mapping = aes(x = Distance, y = Delay)) +
  geom_point(aes(size = count, color = dest), alpha = 1/3) +
  geom_smooth(se = FALSE) +
  geom_text(aes(label=dest),size=2.75) +
  labs(title = "Average distance to destination vs. arrival delay",
      caption = "The circle size shows the number of flights to that destination.")

ggplotly(p + theme(legend.position = "none"))  # Same plot with ggplotly()

`geom_smooth()` using method = 'loess' and formula 'y ~ x'

It looks like delays increase with distance up to ~750 miles and then decrease. Maybe as flights get longer there’s more ability to make up delays in the air?

Can you figure out why we removed “HNL” (Pacific/Honolulu)?

---

Let’s examine if the number of flights during the summer has increased:

flight_count <- flights %>%
  mutate(date = make_datetime(year, month, day)) %>%
  group_by(date) %>%
  summarize(count = n(),  # counting the number of flights (per day)
            delay = mean(arr_delay, na.rm = TRUE)  # average delay (per day)
           )

`summarise()` ungrouping output (override with `.groups` argument)

p <- ggplot(flight_count, aes(date, count)) +
  geom_line() +
  labs(title = "Number of flights out of NYC") +
  theme_classic()

ggplotly(p)

Interestingly, all of these dips are weekends. Let’s check this by creating a bar chart:

flights %>%
  mutate(date = make_datetime(year, month, day),
        weekday = wday(date, label = TRUE)) %>%
  group_by(weekday) %>%
  summarize(count = n(),  # counting the number of flights (per weekday)
            delay = mean(arr_delay, na.rm = TRUE)  # average delay (per weekday)
           ) %>% 
  ggplot(aes(weekday, count)) + 
    geom_bar(stat="identity", width = 0.5) + 
    labs(title="Number of flights per dat of week",
        x = "Day of Week",
        y = "Count") +
    theme_classic() +
    theme(axis.text.x = element_text(angle=65, vjust=0.6))

`summarise()` ungrouping output (override with `.groups` argument)